ALTER PROCEDURE [dbo].[bd_proc_leave_appr_data]
  @order_id AS numeric ,
  @resultStatus AS numeric
AS
BEGIN
	DECLARE @user_serial numeric--人员
	DECLARE @ishaveedu int--是否是额度假，1：调戏，2：年假
	DECLARE @leave_time int--请假时间,
	DECLARE @txsj int--剩余的调休时间
	DECLARE @ho_year int
	DECLARE @ho_month int

	DECLARE @fdnjts int--当前本年法定年假剩余
	DECLARE @flnjts int--当前本年福利年假剩余
	DECLARE @lastfdnj int--当前去年法定年假剩余
	DECLARE @lastflnj int--当前去年福利年假剩余



	set @ho_year = YEAR(GETDATE())
	set @ho_month = MONTH(GETDATE())
	--1、根据@order_id 获取请假人信息，生成游标

	--2、遍历游标，根据@resultStatus的值，来区分驳回或通过
			--根据游标中的leaveId获取请假类型是调休还是年假
		--判断处理调休的驳回、通过；年假的驳回、通过
	begin try
		begin tran
		DECLARE cursor_user CURSOR LOCAL FOR
		select x.user_serial,x.ishaveedu,sum(x.jsjg) from (
select l.user_serial,ISNULL(k.ishaveedu, 0) as ishaveedu,l.jsjg
		from lr_kqingj l
		INNER JOIN kt_qingj k on k.bh = l.bh
		where l.order_id = @order_id
		) x GROUP BY x.user_serial,x.ishaveedu


		OPEN cursor_user FETCH NEXT FROM cursor_user INTO @user_serial,@ishaveedu,@leave_time

		--提取第一条数据存放在变量中
		WHILE @@fetch_status = 0
					BEGIN
						--调休
						if @ishaveedu = 1
							BEGIN
								--通过
								if @resultStatus = 3
									BEGIN
										--将bd_nianjia 表中的冻结调休减去本次请假时间量
										update bd_nianjia
											set sdtx = sdtx - @leave_time
											where user_serial = @user_serial
									END
								--驳回
								if @resultStatus = 2
									BEGIN
										--将bd_nianjia 表中的冻结调休返还给可调休
										update bd_nianjia
											set sdtx = sdtx - @leave_time,
													txts = txts + @leave_time
											where user_serial = @user_serial


										--在bd_kq_holidays中，生产类型为2的记录
										insert into bd_kq_holidays (
											user_serial,
											dept_serial,
											dept_name,
											ho_year,
											ho_month,
											ho_date,
											ho_type,
											last_time,
											use_time,
											remain_time,
											related_id,
											vacation_total
											)
											select
												u.user_serial,
												d.dep_serial,
												d.dep_name,
												@ho_year,
												@ho_month,
												GETDATE(),
												2,
												n.txts,
												@leave_time,
												n.txts,
												@order_id,
												n.txcount
											from dt_user u
												INNER JOIN dt_dep d on u.user_dep = d.dep_serial
												INNER JOIN bd_nianjia n on u.user_serial = n.user_serial
												where u.user_serial = @user_serial
									END
							END
						--年假
						if @ishaveedu = 2
							BEGIN
								--通过
								if @resultStatus = 3
									BEGIN
											--将bd_nianjia 表中的冻结的年假减去本次请假时间
										update bd_nianjia
											set sdnj = sdnj - @leave_time
											where user_serial = @user_serial

									END
								--驳回
								if @resultStatus = 2
									BEGIN
										--根据bd_kq_holidays 中类型3、4、5、6对应的值返还给对应的账户中
										select @lastfdnj = b1.use_time,
											@lastflnj = b2.use_time,
											@fdnjts = b3.use_time,
											@flnjts = b4.use_time
										from dt_user u
										inner join bd_kq_holidays b1 on b1.user_serial  = u.user_serial and b1.ho_type = 3 and b1.related_id = @order_id
										inner join bd_kq_holidays b2 on b2.user_serial  = u.user_serial and b2.ho_type = 4 and b2.related_id = @order_id
										inner join bd_kq_holidays b3 on b3.user_serial  = u.user_serial and b3.ho_type = 5 and b3.related_id = @order_id
										inner join bd_kq_holidays b4 on b4.user_serial  = u.user_serial and b4.ho_type = 6 and b4.related_id = @order_id
										where u.user_serial = @user_serial
										--将bd_nianjia 表中的冻结的年假返还
										update bd_nianjia
											set sdnj = sdnj - @leave_time,
													lastfdnj = lastfdnj + @lastfdnj,
													lastflnj = lastflnj + @lastflnj,
													fdnjts = fdnjts + @fdnjts,
													flnjts = flnjts + @flnjts
											where user_serial = @user_serial

												--在bd_kq_holidays中，生产类型为1的记录
										insert into bd_kq_holidays (
											user_serial,
											dept_serial,
											dept_name,
											ho_year,
											ho_month,
											ho_date,
											ho_type,
											last_time,
											last_time_welfare,
											last_year_time,
											last_year_time_welfare,
											use_time,
											remain_time,
											related_id,
											vacation_total
											)
											select
												u.user_serial,
												d.dep_serial,
												d.dep_name,
												@ho_year,
												@ho_month,
												GETDATE(),
												1,
												n.fdnjts,
												n.flnjts,
												n.lastfdnj,
												n.lastflnj,
												@leave_time,
												(n.fdnjts+n.flnjts+n.lastfdnj+n.lastflnj),
												@order_id,
												n.njcount
											from dt_user u
												INNER JOIN dt_dep d on u.user_dep = d.dep_serial
												INNER JOIN bd_nianjia n on u.user_serial = n.user_serial
												where u.user_serial = @user_serial
									END
							END
						FETCH NEXT FROM cursor_user INTO @user_serial,@ishaveedu,@leave_time
					END
					--将该单子在bd_kq_holidays	中的相关数据的related_result 设置为审批状态,由于删除单子也会走这个存储过程@resultStatus=2，等同驳回。
					--所以这里的related_result不单单指的是真正的驳回，它包含驳回和删除的单子
					update bd_kq_holidays set related_result = @resultStatus where related_id = @order_id


					--2020年5月7日新增，若请假单中存在过去时间，需要将明细中的相关记录做bd_proc_leaveDeductJob存错过程类似的操作
					DECLARE @nowDate varchar(10) = convert(varchar(10),GETDATE(),120)
						select @ishaveedu =  ISNULL(k.ishaveedu, 0) from bd_leave_order l INNER JOIN kt_qingj k on k.bh = l.leave_id
								where l.order_id = @order_id
					--调休且审批通过
					if (@ishaveedu = 1 and @resultStatus = 3)
						BEGIN
							--待扣除的人员名单和对应的值
							create table #leave_deduct(
								user_serial bigint not null,
								deduct_value int DEFAULT 0
							)
						--关联请假单和请假明细表，条件：审批通过的单子，明细表中有效状态为有效，请假日期在@beginDate，@endDate之间，是否已扣除为null
						--统计每人的总时长
						insert into #leave_deduct
						select x.user_serial,sum(x.jsjg) as deduct_value from (
							select l.user_serial,jsjg from lr_kqingj l
							where
								l.order_id = @order_id
								and l.kssj <= @nowDate) x GROUP BY x.user_serial

						--扣除使用
						update n
							set n.real_txts = n.real_txts - t.deduct_value
						from bd_nianjia n
						INNER JOIN #leave_deduct t on n.user_serial = t.user_serial

						--给明细记录打标机
						update l
								set is_deduct = '1'
							from lr_kqingj l
							where
								l.order_id = @order_id
								and l.kssj <= @nowDate
						END

					--重新计算考勤
					DECLARE @users VARCHAR(MAX) = ''
					DECLARE @startDate DATE
					DECLARE @endDate DATE

					SELECT
						@users = @users + CAST(u.user_serial AS VARCHAR(100)) + ','
					FROM (
							select DISTINCT user_serial from lr_kqingj where order_id = @order_id
					) u

					SET @users = SUBSTRING(@users,0, LEN(@users))
					SELECT @startDate = start_date, @endDate = end_date  from bd_leave_order where order_id = @order_id
					EXEC bd_proc_timecard_extend @startDate, @endDate, @users,6
		commit tran
	end try
	begin catch
			DECLARE @errStr VARCHAR(MAX) = ERROR_MESSAGE();
			IF XACT_STATE() <> 0
			BEGIN
			  ROLLBACK TRANSACTION;
			  RAISERROR (@errStr, 16, 1);
				CLOSE cursor_user --关闭游标
				DEALLOCATE cursor_user --释放游标
			END;
	end catch



END